﻿Imports System.Collections.Generic
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient

''' <summary>
''' sql数据库大批量操作
''' </summary>
Public Class MSsqlBulkCopy
#Region "Insert"
    ''' <summary>
    ''' 大批量添加数据ds.Tables[0] 表名strTblName(数据库表名称)
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <param name="strTblName"></param>
    ''' <returns>受影响的行数</returns>
    Public Shared Function SqlBulkInsert(ByVal dt As DataTable, ByVal strTblName As String, ByVal conn As SqlConnection) As Integer
        Dim affect As Integer = 0
        Try
            Dim myCommand As New SqlCommand("select * from " & strTblName, conn)
            Dim myAdapter As New SqlDataAdapter(myCommand)
            Dim myCommandBuilder As New SqlCommandBuilder(myAdapter)
            myAdapter.InsertCommand = myCommandBuilder.GetInsertCommand()

            For Each dr As DataRow In dt.Rows
                If dr.RowState <> DataRowState.Added Then
                    dr.SetAdded()
                End If
            Next
            conn.Open()
            affect = myAdapter.Update(dt)
            ' ds.AcceptChanges();
            ' return ds; 
            conn.Close()
        Catch err As Exception
            conn.Close()
            Throw err
        End Try
        Return affect
    End Function
#End Region

#Region "Update"
    ''' <summary>
    ''' 大批量更改数据ds.Tables[0] 表名strTblName(数据库表名称,表需要有主键)
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <param name="strTblName"></param>
    ''' <returns>受影响的行数</returns>
    Public Shared Function SqlBulkUpdate(ByVal dt As DataTable, ByVal strTblName As String, ByVal conn As SqlConnection) As Integer
        Dim affect As Integer = 0
        Try
            'foreach (DataRow dr in dt.Rows)
            '{
            '    if (dr.RowState == DataRowState.Added)
            '    {
            '        dr[0] = dr[0];
            '        dr.AcceptChanges();
            '        dr.SetModified();
            '    }
            '}           


            Dim myCommand As New SqlCommand("select * from " & strTblName, conn)
            Dim myAdapter As New SqlDataAdapter(myCommand)
            Dim myCommandBuilder As New SqlCommandBuilder(myAdapter)
            myAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand()
            conn.Open()
            affect = myAdapter.Update(dt)
            ' ds.AcceptChanges();
            ' return ds;    //数据集的行状态在更新后会都变为: UnChange,在这次更新后客户端要用返回的ds 
            conn.Close()
        Catch err As Exception
            conn.Close()
            Throw err
        End Try
        Return affect
    End Function

    ''' <summary>
    ''' 大批量更改数据ds.Tables[0] 表名strTblName(数据库表名称,表需要有主键)
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <param name="strTblName"></param>
    ''' <returns>受影响的行数</returns>
    Public Shared Function SqlBulkUpdate(ByVal dt As DataTable, ByVal strTblName As String, ByVal conn As SqlConnection, ByVal updatecmd As SqlCommand) As Integer
        Dim affect As Integer = 0
        Try
            'foreach (DataRow dr in dt.Rows)
            '{
            '    if (dr.RowState == DataRowState.Added)
            '    {
            '        dr[0] = dr[0];
            '        dr.AcceptChanges();
            '        dr.SetModified();
            '    }
            '}           


            Dim myCommand As New SqlCommand("select * from " & strTblName, conn)
            Dim myAdapter As New SqlDataAdapter(myCommand)
            Dim myCommandBuilder As New SqlCommandBuilder(myAdapter)
            myAdapter.UpdateCommand = updatecmd
            conn.Open()
            affect = myAdapter.Update(dt)
            ' ds.AcceptChanges();
            ' return ds;    //数据集的行状态在更新后会都变为: UnChange,在这次更新后客户端要用返回的ds 
            conn.Close()
        Catch err As Exception
            conn.Close()
            Throw err
        End Try
        Return affect
    End Function
#End Region
End Class